package com.dao;

import com.bean.Book;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import com.utils.DBCP;

import java.util.ArrayList;
import java.util.List;


public class BooksUtilsDao {
    public static QueryRunner runner = new QueryRunner(DBCP.getDataSource());
    //查询书表
    @SuppressWarnings({ "unchecked", "rawtypes" })
	public List<Book> findAll() throws Exception{
        ArrayList<Book> list = new ArrayList<Book>();
        String sql = "select * from books";
        list = (ArrayList<Book>) runner.query(sql, new BeanListHandler(Book.class));
        return list;
    }
    //根据id查询书的信息
    @SuppressWarnings({ "unchecked", "rawtypes" })
	public List<Book> findId(String id) throws Exception{
        ArrayList<Book> list = new ArrayList<Book>();
        String sql = "select * from books where id = ?";
        list = (ArrayList<Book>) runner.query(sql, new BeanListHandler(Book.class), id);
        return list;
    }

    //根据id增加书的数量
    public void add(String id) throws Exception{
        String sql = "update books set number = number+'1' where id = ?";
        runner.update(sql, new Object[]{id});
    }

    //根据id减少书的数量
    public void reduce(String id) throws Exception{
        String sql = "update books set number = number-'1' where id = ?";
        runner.update(sql, new Object[]{id});

    }

    //清空购物车
    public void remove(String id) throws Exception{
        String sql = "update books set number = 0, totalprice = 0 where id = ?";
        runner.update(sql, new Object[]{id});

    }
    //计算购物车单本图书的总价格（小计）
    public void totalprice(String id) throws Exception{
        String sql = "update books set totalprice = price*number where id = ?";
        runner.update(sql, new Object[]{id});

    }

    //计算购物车总价格
    public double sumprice() throws Exception{
        String sql = "select sum(totalprice) from books ";
        @SuppressWarnings({ "unchecked", "rawtypes" })
		double sumprice = (double) runner.query(sql, new ScalarHandler());
        return sumprice;
    }

    //计算购物车总数量
    public double sumnumber() throws Exception{
        String sql = "select sum(number) from books ";
        @SuppressWarnings({ "unchecked", "rawtypes" })
		double sumnumber = (double) runner.query(sql, new ScalarHandler());
        return sumnumber;
    }

    //将数据库购物车信息删除
    public void deletecart() throws Exception{
        String sql = "update books set number = 0, totalprice = 0";
        runner.update(sql);
    }

}
